﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Configuration;

namespace FormsAppT8
{
    class dbconn
    {
        public static SqlConnection DBCon()
        {
            //string s = "server=172.31.2.2;database=T9EAM;uid=sa;pwd=nbdg.sa2012";
           //string s = "server=121.40.240.45;database=T8EDSV2025;uid=sa;pwd=nbzhzn.sa2012";
             string s = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            return new SqlConnection(s);
        }




        public void sqlcommand(string sql)
        {
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("sqlcommand:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
        }

        public void databind(string sql, string table, DataGridView gv)
        {
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sda1 = new SqlDataAdapter(sql, conn);
                DataSet ds1 = new DataSet();
                sda1.Fill(ds1, table);
                if (ds1.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                    {
                        DataRow row = ds1.Tables[0].Rows[i];
                        int num = ds1.Tables[0].Columns.Count;
                        string[] values = new string[num];
                        for (int j = 0; j < num; j++)
                        {
                            values[j] = row[j].ToString();
                        }
                        gv.Rows.Add(values);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("databind:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
        }

        public void bdlm(string sql, string table, DataGridView gv)
        {
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sda1 = new SqlDataAdapter(sql, conn);
                DataSet ds1 = new DataSet();
                sda1.Fill(ds1, table);
                for (int m = 0; m < ds1.Tables[0].Columns.Count; m++)
                {
                    DataGridViewTextBoxColumn acCode = new DataGridViewTextBoxColumn();
                    acCode.Name =m.ToString();
                    acCode.DataPropertyName = m.ToString();
                    acCode.HeaderText = ds1.Tables[0].Columns[m].ColumnName;
                    gv.Columns.Add(acCode);
                }

                    if (ds1.Tables[0].Rows.Count > 0)
                    {
                        for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
                        {
                            DataRow row = ds1.Tables[0].Rows[i];
                            int num = ds1.Tables[0].Columns.Count;
                            string[] values = new string[num];
                            for (int j = 0; j < num; j++)
                            {
                                values[j] = row[j].ToString();
                            }
                            gv.Rows.Add(values);
                        }
                    }
            }
            catch (Exception ex)
            {
                MessageBox.Show("databind:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
        }


        public static int rowcount(string sql, string table)
        {
            int count = 0;
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sda1 = new SqlDataAdapter(sql, conn);
                DataSet ds1 = new DataSet();
                sda1.Fill(ds1, table);
                count = ds1.Tables[0].Rows.Count;
            }
            catch (Exception ex)
            {
                MessageBox.Show("rowcount:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
            return count;
        }
        public static String data(string sql, string table, string ziduan)
        {
            string a = "";
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sdasv = new SqlDataAdapter(sql, conn);
                DataSet dssv = new DataSet();
                sdasv.Fill(dssv, table);
                int countsv = dssv.Tables[0].Rows.Count;
                if (countsv == 0)
                {
                    a = "";
                }
                else
                {
                    a = dssv.Tables[0].Rows[0][ziduan].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("data:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
            return a;
        }
        public void combind(string sql, string table, string id, string name, ComboBox com1)
        {
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sdaps1 = new SqlDataAdapter(sql, conn);
                DataSet dsps1 = new DataSet();
                sdaps1.Fill(dsps1, table);
                com1.DisplayMember = name; //显示到comboBox的值
                com1.ValueMember = id; //comboBox真正的值
                com1.DataSource = dsps1.Tables[0];//绑定数据源
            }
            catch (Exception ex)
            {
                MessageBox.Show("combind:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
        }
        public void combind1(string sql, string table, string id, string name, DataGridViewComboBoxColumn com1)
        {
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sdaps1 = new SqlDataAdapter(sql, conn);
                DataSet dsps1 = new DataSet();
                sdaps1.Fill(dsps1, table);
                com1.DisplayMember = name; //显示到comboBox的值
                com1.ValueMember = id; //comboBox真正的值
                com1.DataSource = dsps1.Tables[0];//绑定数据源
            }
            catch (Exception ex)
            {
                MessageBox.Show("combind1:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
        }

        public static String data1(string sql, string table, string ziduan1, string ziduan2)
        {
            string a = "";
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sdasv = new SqlDataAdapter(sql, conn);
                DataSet dssv = new DataSet();
                sdasv.Fill(dssv, table);
                int countsv = dssv.Tables[0].Rows.Count;
                if (countsv == 0)
                {
                    a = "";
                }
                else
                {
                    for (int i = 0; i < countsv; i++)
                    {
                        a = a + "\n" + "销售订单号：" + dssv.Tables[0].Rows[i][ziduan1].ToString() + "未出数量：" + Convert.ToDouble(dssv.Tables[0].Rows[i][ziduan2].ToString()).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("data:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
            return a;
        }
        public static String data2(string sql, string table, string ziduan1, string ziduan2)
        {
            string a = "";
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sdasv = new SqlDataAdapter(sql, conn);
                DataSet dssv = new DataSet();
                sdasv.Fill(dssv, table);
                int countsv = dssv.Tables[0].Rows.Count;
                if (countsv == 0)
                {
                    a = "";
                }
                else
                {
                    for (int i = 0; i < countsv; i++)
                    {
                        a = a + "\n" + "储位：" + dssv.Tables[0].Rows[i][ziduan1].ToString() + "  数量：" + Convert.ToDouble(dssv.Tables[0].Rows[i][ziduan2].ToString()).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("data:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
            return a;
        }

        public static String data3(string sql, string table, string ziduan1, string ziduan2)
        {
            double b = 0;
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sdasv = new SqlDataAdapter(sql, conn);
                DataSet dssv = new DataSet();
                sdasv.Fill(dssv, table);
                int countsv = dssv.Tables[0].Rows.Count;
                if (countsv == 0)
                {
                    b = 0;
                }
                else
                {
                    for (int i = 0; i < countsv; i++)
                    {
                        b = b + Convert.ToDouble(dssv.Tables[0].Rows[i][ziduan2].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("data:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
            return b.ToString();
        }

        public static String cw(string sql, string table, string ziduan1, string zd2)
        {
            string a = "";
            SqlConnection conn = DBCon();
            try
            {
                conn.Open();
                SqlDataAdapter sdasv = new SqlDataAdapter(sql, conn);
                DataSet dssv = new DataSet();
                sdasv.Fill(dssv, table);
                int countsv = dssv.Tables[0].Rows.Count;
                if (countsv == 0)
                {
                    a = "";
                }
                else
                {
                    for (int i = 0; i < countsv; i++)
                    {
                        if (i == 0)
                        {
                            a = dssv.Tables[0].Rows[i][ziduan1].ToString() + ":" + dssv.Tables[0].Rows[i][zd2].ToString();
                        }
                        else
                        {
                            a = a + "/" + dssv.Tables[0].Rows[i][ziduan1].ToString() + ":" + dssv.Tables[0].Rows[i][zd2].ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("data:" + sql + "\n" + ex.ToString());
            }
            finally { conn.Close(); }
            return a;
        }
    }
}
